﻿using NP.BPMReportPlatform.AUTO;
using NP.BPMReportPlatform.AUTO.Adjust.Dto;
using NP.BPMReportPlatform.AUTO.Quotation.Dto;
using NP.BPMReportPlatform.AUTO.TechnicalDependence.Dto;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.Utils;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;

namespace NP.BPMReportPlatform.Auto
{
    /// <summary>
    /// AUTO服务
    /// </summary>
    public class AUTOAppService : BPMReportPlatformAppService, IAUTOAppService
    {
        private readonly SqlSugarClient _db, _dbEngine;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sqlSugarAppService"></param>
        public AUTOAppService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp, IBPMPlatformAppService iBPMPlatformApp)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
        }

        #region 样品单流程 报表
        public async Task<PagedResultDto<GetSampleListOutput>> GetAllAsync(GetSampleListInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);

            listViewDto.ForEach(x => x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId));

            //返回结果
            return new PagedResultDto<GetSampleListOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        public async Task<List<GetSampleListOutput>> ExportAsync(GetSampleListInput input)
        {
            var listQueryable = GetQueryableToExel(input);
            var listViewDto = await listQueryable.ToListAsync();
            return listViewDto;
        }


        private ISugarQueryable<GetSampleListOutput> GetQueryable(GetSampleListInput input)
        {
            var listQueryable = _db.Queryable<SampleListHeader, BusinessProcessesNew>(
                     (x, b) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == b.InstanceId))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.CustomerGroup2.Contains(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber == input.InstanceNumber)
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, b) => SqlFunc.ContainsArray(input.OrderStatus.ToArray(), b.ProcessStatusCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Contains(input.AddUserName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.BusinessType), x => x.BusinessType.Contains(input.BusinessType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerCode.Contains(input.CustomerNo))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName == input.CustomerName)
                .WhereIF(!input.GrantType.IsNullOrEmpty(), x => SqlFunc.ContainsArray(input.GrantType.ToArray(), x.GrantType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.IsToSales), x => x.IsToSales == input.IsToSales)
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null,x => x.AddTime >= input.StartCreationTime && x.AddTime <= input.EndCreationTime)
                .Select((x,  b) => new GetSampleListOutput()
                {
                    InstanceNumber = x.InstanceNumber,
                    AddTime = x.AddTime,
                    AddUserName = x.AddUserName,
                    OrderStatus = b.ProcessStatusName,
                    SapStatus =x.SapStatus,
                    BusinessType =x.BusinessType,
                    SaleRegion = x.SaleRegion,
                    CustomerGroup2 = x.CustomerGroup2,
                    CustomerCode =x.CustomerCode,
                    CustomerName =x.CustomerName,
                    IsToSales = x.IsToSales,
                    //SalespersonName =x.SalespersonName,
                    //NneedType =x.NneedType,
                    //CustomerGroup4 =x.CustomerGroup4,
                    //CustomerRank=x.CustomerRank,
                    //CostCentre =x.CostCentre,
                    //CostCentreName =x.CostCentreName,
                    //GrantType =x.GrantType,
                    //ArriveTime=x.ArriveTime,
                    //ComplaintNo =x.ComplaintNo,
                    //IsTC =x.IsTC,
                    //TcTime =x.TcTime,
                    //PaybackPeriod =x.PaybackPeriod,
                    //ToSalesTime =x.ToSalesTime,
                    //ReceiverCode= x.ReceiverCode,
                    //ReceiverName =x.ReceiverName,
                    //DeliverType =x.DeliverType,
                    //Consignee =x.Consignee,
                    //Telephone =x.Telephone,
                    //DeliverAddress =x.DeliverAddress,
                    //TransitArea =x.TransitArea,
                    //TransitAreaCode =x.TransitAreaCode,
                    //RequireDesc =x.RequireDesc,
                    //SaleSuperior =x.SaleSuperior,
                    //XSXZName =x.XSXZName,
                    //CostTotal= x.CostTotal,

                }).MergeTable().Distinct().OrderBy(ins => ins.AddTime, OrderByType.Desc);
            return listQueryable;
        }

        private ISugarQueryable<GetSampleListOutput> GetQueryableToExel(GetSampleListInput input)
        {
            var listQueryable = _db.Queryable<SampleListHeader, SampleListDetail, BusinessProcessesNew>(
                     (x, d, b) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == d.InstanceNumber, JoinType.Left, x.InstanceNumber == b.InstanceId))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.CustomerGroup2.Contains(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber == input.InstanceNumber)
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, d, b) => SqlFunc.ContainsArray(input.OrderStatus.ToArray(), b.ProcessStatusCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Contains(input.AddUserName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.BusinessType), x => x.BusinessType.Contains(input.BusinessType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerCode.Contains(input.CustomerNo))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName == input.CustomerName)
                .WhereIF(!input.GrantType.IsNullOrEmpty(), x => SqlFunc.ContainsArray(input.GrantType.ToArray(), x.GrantType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.IsToSales), x => x.IsToSales == input.IsToSales)
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime <= input.EndCreationTime)
                .Select((x, d, b) => new GetSampleListOutput()
                {
                    InstanceNumber = x.InstanceNumber,
                    AddTime = x.AddTime,
                    AddUserName = x.AddUserName,
                    OrderStatus = b.ProcessStatusName,
                    SapStatus = x.SapStatus,
                    BusinessType = x.BusinessType,
                    SalespersonName = x.SalespersonName,
                    NneedType = x.NneedType,
                    CustomerCode = x.CustomerCode,
                    CustomerName = x.CustomerName,
                    SaleRegion = x.SaleRegion,
                    CustomerGroup2 = x.CustomerGroup2,
                    CustomerGroup4 = x.CustomerGroup4,
                    CustomerRank = x.CustomerRank,
                    CostCentre = x.CostCentre,
                    CostCentreName = x.CostCentreName,
                    BusinessOpportunityName = x.BusinessOpportunityName,
                    BusinessOpportunityNo =x.BusinessOpportunityNo,
                    GrantType = x.GrantType,
                    ArriveTime = x.ArriveTime,
                    ComplaintNo = x.ComplaintNo,
                    IsTC = x.IsTC,
                    TcTime = x.TcTime,
                    PaybackPeriod = x.PaybackPeriod,
                    IsToSales = x.IsToSales,
                    ToSalesTime = x.ToSalesTime,
                    ReceiverCode = x.ReceiverCode,
                    ReceiverName = x.ReceiverName,
                    DeliverType = x.DeliverType,
                    Consignee = x.Consignee,
                    Telephone = x.Telephone,
                    DeliverAddress = x.DeliverAddress,
                    TransitArea = x.TransitArea,
                    TransitAreaCode = x.TransitAreaCode,
                    RequireDesc = x.RequireDesc,
                    SaleSuperior = x.SaleSuperior,
                    XSXZName = x.XSXZName,
                    CostTotal = x.CostTotal,
                    SortNo = d.SortNo,
                    ProductCode = d.ProductCode,
                    ProductName = d.ProductName,
                    PDT = d.PDT,
                    PDTDesc = d.PDTDesc,
                    Packing = d.Packing,
                    Unit = d.Unit,
                    Number = d.Number,
                    Cost = d.Cost,
                    CostSum = d.CostSum,
                    Factory = d.Factory,
                    Inventory = d.Inventory,
                    Batch = d.Batch
                }).MergeTable().Distinct().OrderBy(ins => ins.AddTime, OrderByType.Desc);
            return listQueryable;
        }
        #endregion

        #region  销售技术依赖书报表
        /// <summary>
        /// 获取Queryable(报表导出-新品开发及试作品准备)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<ExportTD1Output> GetQueryableToExel1(TechnicalDependenceInput input)
        {
            ////业务需求，以下都属于处理中
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<TechnicalDependenceHeader, TechnicalDependenceDetail2, BusinessProcessesNew>(
                    (x, d, bu) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == d.InstanceNumber, JoinType.Left, x.InstanceNumber == bu.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), (x, d, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, d, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.ReceiveFactory), x => x.ReceiveFactory.Equals(input.ReceiveFactory))
                .WhereIF(!string.IsNullOrWhiteSpace(input.SalesDependencyType), x => x.SalesDependencyType.Equals(input.SalesDependencyType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.SubAutoType), x => x.SubAutoType.Equals(input.SubAutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null,
                x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .Select((x, d, bu) =>
                    new ExportTD1Output()
                    {
                        InstanceNumber = x.InstanceNumber,
                        ReceiveFactory = x.ReceiveFactory,
                        AutoType = x.AutoType,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        AddUserName =x.AddUserName,
                        CreateTime = bu.StartTime.ToString(),
                        OrderStatus = bu.ProcessStatusName,
                        SalesDependencyType=x.SalesDependencyType,
                        PaintName = d.PaintName,
                        SampleQty = d.SampleQty,
                        SingleWeight = d.SingleWeight,
                        Barrels = d.Barrels,
                        TotalWeight = d.TotalWeight,
                        ArrivalTime = d.ArrivalTime,
                        ExpectedCost = d.ExpectedCost,
                        IsInvoicing = d.IsInvoicing,
                        MassProductionTime = d.MassProductionTime,
                        MonthlyDosage = d.MonthlyDosage,
                        SpecialNeeds = d.SpecialNeeds,
                        TrialTimes = d.TrialTimes,
                        TrialNumber = d.TrialNumber,
                        PlanCompletionTime = d.PlanCompletionTime.Substring(0,10),
                        Suggestion = d.Suggestion,
                        FinishTime = d.FinishTime,
                        ActualFinishWeight = d.ActualFinishWeight,
                        LineTestResult = d.LineTestResult
                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }
        /// <summary>
        /// 获取Queryable(报表导出-性能测试)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<ExportTD2Output> GetQueryableToExel2(TechnicalDependenceInput input)
        {
            ////业务需求，以下都属于处理中
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<TechnicalDependenceHeader, TechnicalDependenceDetail3, BusinessProcessesNew>(
                    (x, d, bu) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == d.InstanceNumber, JoinType.Left, x.InstanceNumber == bu.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), (x, d, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, d, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.ReceiveFactory), x => x.ReceiveFactory.Equals(input.ReceiveFactory))
                .WhereIF(!string.IsNullOrWhiteSpace(input.SalesDependencyType), x => x.SalesDependencyType.Equals(input.SalesDependencyType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.SubAutoType), x => x.SubAutoType.Equals(input.SubAutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null,
                x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .Select((x, d, bu) =>
                    new ExportTD2Output()
                    {
                        InstanceNumber = x.InstanceNumber,
                        ReceiveFactory = x.ReceiveFactory,
                        AutoType = x.AutoType,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        AddUserName = x.AddUserName,
                        CreateTime = bu.StartTime.ToString(),
                        OrderStatus = bu.ProcessStatusName,
                        SalesDependencyType = x.SalesDependencyType,
                        ProductName=d.ProductName,
                        ProductCode=d.ProductCode,
                        Suggestion=d.Suggestion,
                        ProjectName=d.ProjectName,
                        TestParty=d.TestParty,
                        CompletionTime = d.CompletionTime,
                        ActualCompletionTime = d.ActualCompletionTime,
                        IsSatisfied = d.IsSatisfied,
                        Reason = d.Reason,

                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }

        /// <summary>
        /// 获取Queryable(报表导出-技术服务现场支持)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<ExportTD3Output> GetQueryableToExel3(TechnicalDependenceInput input)
        {
            ////业务需求，以下都属于处理中
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<TechnicalDependenceHeader, TechnicalDependenceDetail4, BusinessProcessesNew>(
                    (x, d, bu) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == d.InstanceNumber, JoinType.Left, x.InstanceNumber == bu.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), (x, d, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, d, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.ReceiveFactory), x => x.ReceiveFactory.Equals(input.ReceiveFactory))
                .WhereIF(!string.IsNullOrWhiteSpace(input.SalesDependencyType), x => x.SalesDependencyType.Equals(input.SalesDependencyType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.SubAutoType), x => x.SubAutoType.Equals(input.SubAutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null,
                x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .Select((x, d, bu) =>
                    new ExportTD3Output()
                    {
                        InstanceNumber = x.InstanceNumber,
                        ReceiveFactory = x.ReceiveFactory,
                        AutoType = x.AutoType,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        AddUserName = x.AddUserName,
                        CreateTime = bu.StartTime.ToString(),
                        OrderStatus = bu.ProcessStatusName,
                        SalesDependencyType = x.SalesDependencyType,
                        ServiceContent = d.ServiceContent,
                        ServiceDescription = d.ServiceDescription,
                        Suggestion = d.Suggestion,
                        CompletionTime = d.CompletionTime,
                        ActualCompletionTime = d.ActualCompletionTime,
                        IsSatisfied = d.IsSatisfied,
                        Reason = d.Reason,

                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }

        /// <summary>
        /// 获取Queryable(报表展现)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<TechnicalDependenceOutput> GetQueryable(TechnicalDependenceInput input)
        {
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<TechnicalDependenceHeader, BusinessProcessesNew, TechnicalDependenceDetail2>(
                (x, bu,t2) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId, JoinType.Left, x.InstanceNumber == t2.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), (x, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.ReceiveFactory), x => x.ReceiveFactory.Equals(input.ReceiveFactory))
                .WhereIF(!string.IsNullOrWhiteSpace(input.SalesDependencyType), x => x.SalesDependencyType.Equals(input.SalesDependencyType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.SubAutoType), x => x.SubAutoType.Equals(input.SubAutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .Select((x, bu,t2) =>
                    new TechnicalDependenceOutput()
                    {
                        InstanceNumber = x.InstanceNumber,
                        AutoType = x.AutoType,
                        OrderStatus = bu.ProcessStatusName,
                        ReceiveFactory= x.ReceiveFactory,
                        CreateTime = bu.StartTime.ToString(),
                        UserName = bu.StartUserName,
                        SalesDependencyType=x.SalesDependencyType,
                        PaintName=t2.PaintName,
                        TrialNumber=t2.TrialNumber,
                        TrialTimes=t2.TrialTimes,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        SyncStatus = bu.ProcessStatusName,
                        LineTestResult=t2.LineTestResult,
                        
                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);
            return listQueryable;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<TechnicalDependenceOutput>> GetAllAsync(TechnicalDependenceInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
            listViewDto.ForEach(x =>
            {
                x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
            });
            //返回结果
            return new PagedResultDto<TechnicalDependenceOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        /// <summary>
        /// 报表导出-新品开发及试作品准备
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<ExportTD1Output>> Export1Async(TechnicalDependenceInput input)
        {
            var listQueryable = GetQueryableToExel1(input);
            var listViewDto = await listQueryable.ToListAsync();
            ////修改导出的报表数据
            //foreach (var item in listViewDto)
            //{
            //    if (item.OrderStatus != null && (item.OrderStatus == "发起人撤回" || item.OrderStatus.Contains("退回发起人")))
            //    {
            //        item.OrderStatus = "处理中";
            //    }
            //}
            var i = 1;
            listViewDto.ForEach(x => x.SerialNumber = i++.ToString());
            return listViewDto;
        }

        /// <summary>
        /// 报表导出-性能测试
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<ExportTD2Output>> Export2Async(TechnicalDependenceInput input)
        {
            var listQueryable = GetQueryableToExel2(input);
            var listViewDto = await listQueryable.ToListAsync();
            ////修改导出的报表数据
            //foreach (var item in listViewDto)
            //{
            //    if (item.OrderStatus != null && (item.OrderStatus == "发起人撤回" || item.OrderStatus.Contains("退回发起人")))
            //    {
            //        item.OrderStatus = "处理中";
            //    }
            //}
            var i = 1;
            listViewDto.ForEach(x => x.SerialNumber = i++.ToString());
            return listViewDto;
        }
        /// <summary>
        /// 报表导出-技术服务现场支持
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<ExportTD3Output>> Export3Async(TechnicalDependenceInput input)
        {
            var listQueryable = GetQueryableToExel3(input);
            var listViewDto = await listQueryable.ToListAsync();
            ////修改导出的报表数据
            //foreach (var item in listViewDto)
            //{
            //    if (item.OrderStatus != null && (item.OrderStatus == "发起人撤回" || item.OrderStatus.Contains("退回发起人")))
            //    {
            //        item.OrderStatus = "处理中";
            //    }
            //}
            var i = 1;
            listViewDto.ForEach(x => x.SerialNumber = i++.ToString());
            return listViewDto;
        }
        #endregion
        #region KG报价报表
        /// <summary>
        /// 获取Queryable(报表导出)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<ExportKGQuotationOutput> GetQueryableToExel(GetKGQuotationInput input)
        {
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<QuotationHeader,QuotationCPUType1Detail, BusinessProcessesNew>(
                    (x ,d, bu) => new JoinQueryInfos(JoinType.Inner, x.InstanceNumber == d.InstanceNumber,JoinType.Left, x.InstanceNumber == bu.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber),(x, d, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName),x => x.AddUserName.Equals(input.AddUserName))
                 .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x,d, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                 .WhereIF(!input.SaleRegion.IsNullOrEmpty(), (x, d, bu) => SqlFunc.ContainsArray<string>(input.SaleRegion.ToArray(), x.SaleRegion))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType),x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AutoType2), x => x.AutoType2.Equals(input.AutoType2))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1)<= input.EndCreationTime)
                .WhereIF(input.StartValidTime != null && input.EndValidTime != null, x => Convert.ToDateTime(x.PriceEndtime) >= input.StartValidTime && Convert.ToDateTime(x.PriceEndtime).AddDays(-1) <= input.EndValidTime)
                .Where(x => x.ProcessName.Equals("KG报价"))
                .Select((x, d, bu) =>
                    new ExportKGQuotationOutput()
                    {  
                        InstanceNumber = x.InstanceNumber,
                        CreateTime = bu.StartTime.ToString(),
                        OrderStatus = bu.ProcessStatusName,
                        SaleRegion = x.SaleRegion,
                        AppoverType = x.AppoverType,
                        AutoType =x.AutoType,
                        AutoType2 = x.AutoType2,
                        CustomerStatus = x.CustomerStatus,
                        CustomerNo=x.CustomerNO,
                        CustomerName=x.CustomerName,
                        CustomerRank=x.CustomerRank,
                        CompanyType=x.CompanyType,
                        CustomerCatalog = x.CustomerCatalog,
                        DeliverType=x.DeliverType,
                        BalanceType=x.BalanceType,
                        PriceEndtime=x.PriceEndtime,
                        ProductCode=d.ProductCode,
                        ProductName=d.ProductName,
                        PDT = d.PDT,
                        PDTDesc = d.PDTDesc,
                        SaleRegion1 =d.SaleRegion,
                        ProductType=d.ProductType,
                        SalesUnit=d.SalesUnit,
                        ProposedContractPrice=d.ProposedContractPrice,
                        NoTaxProposedContractPrice=d.NoTaxProposedContractPrice.ToString(),
                        Competitor=d.Competitor,
                        CompetitorPrice=d.CompetitorPrice.ToString(),
                        IsNew=d.IsNew,
                        FixedMonthlyAmount=d.FixedMonthlyAmount,
                        ProcessingFee=d.ProcessingFee.ToString(),
                        ProcessingFeeRatio= d.ProcessingFeeRatio.ToString(),
                        RMC=d.RMC.ToString(),
                        RMC2 = d.RMC2.ToString(),
                        RMCRatio= d.RMCRatio.ToString(),
                        RMC2Ratio=d.RMC2Ratio.ToString() ,
                        RMC2ErroPrompt=d.RMC2ErroPrompt,
                        GrossProfit1=d.GrossProfit1.ToString(),
                        GrossProfit2=d.GrossProfit2.ToString() ,
                        TheoreticalPrice=d.TheoreticalPrice.ToString(),
                        PriceEstimation1=d.PriceEstimation1,
                        PriceEstimation2=d.PriceEstimation2,
                        Remark=d.Remark,
                        SyncSAP=d.SyncSAP,
                        ApproveDate = bu.FinishTime != null ? bu.FinishTime.Value.ToString("yyyy-MM-dd") : "",
                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }
        /// <summary>
        /// 获取Queryable(报表展现)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<KGQuotationOutput> GetQueryable(GetKGQuotationInput input)
        {
            //if (!input.OrderStatus.IsNullOrEmpty()&& SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(),"处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<QuotationHeader, BusinessProcessesNew, MailSendLog>(
                    (x, bu, m) => new JoinQueryInfos(
                        JoinType.Left, x.InstanceNumber == bu.InstanceId, JoinType.Left, x.InstanceNumber == m.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), (x, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                .WhereIF(!input.SaleRegion.IsNullOrEmpty(), (x, bu) => SqlFunc.ContainsArray<string>(input.SaleRegion.ToArray(), x.SaleRegion))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AutoType2), x => x.AutoType2.Equals(input.AutoType2))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .WhereIF(input.StartValidTime != null && input.EndValidTime != null, x => Convert.ToDateTime(x.PriceEndtime) >= input.StartValidTime && Convert.ToDateTime(x.PriceEndtime).AddDays(-1) <= input.EndValidTime)
                .Where((x, bu, m) => x.ProcessName.Equals("KG报价"))
                .Select((x, bu, m) =>
                    new KGQuotationOutput()
                    {
                        InstanceNumber = x.InstanceNumber,
                        SaleRegion = x.SaleRegion,
                        AutoType = x.AutoType,
                        OrderStatus = bu.ProcessStatusName,
                        CreateTime = bu.StartTime.ToString(),
                        PriceEndtime =x.PriceEndtime,
                        UserName = bu.StartUserName,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        SyncStatus = m.Status,
                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);  
            return listQueryable;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<KGQuotationOutput>> GetAllAsync(GetKGQuotationInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
            listViewDto.ForEach(x => {
                x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
                switch (x.SyncStatus)
                {
                    case "1":
                        x.SyncStatusName = "同步失败";
                        break;
                    case "0":
                        x.SyncStatusName = "同步成功";
                        break;
                    default:
                        if (!x.OrderStatus.IsNullOrWhiteSpace() && (x.OrderStatus.Contains("终止") || x.OrderStatus.Contains("拒绝")))
                        {
                            x.SyncStatusName = "";
                        }
                        else if(!x.OrderStatus.IsNullOrWhiteSpace() && x.OrderStatus.Contains("通过"))
                        {
                            x.SyncStatusName = "暂无同步记录";
                        }else
                        {
                            x.SyncStatusName = "";
                        }
                        break;
                }
            });
            //返回结果
            return new PagedResultDto<KGQuotationOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<ExportKGQuotationOutput>> ExportAsync(GetKGQuotationInput input)
        {
            var listQueryable = GetQueryableToExel(input);
            var listViewDto = await listQueryable.ToListAsync();
            //修改导出的报表数据
            foreach (var item in listViewDto)
            {
                item.ProcessingFeeRatio = !string.IsNullOrEmpty(item.ProcessingFeeRatio) ? item.ProcessingFeeRatio + "%": item.ProcessingFeeRatio;
                item.RMCRatio = !string.IsNullOrEmpty(item.RMCRatio) ? item.RMCRatio + "%" : item.RMCRatio;
                item.RMC2Ratio = !string.IsNullOrEmpty(item.RMC2Ratio) ? item.RMC2Ratio + "%" : item.RMC2Ratio;
                item.GrossProfit1= !string.IsNullOrEmpty(item.GrossProfit1) ? item.GrossProfit1 + "%" : item.GrossProfit1;
                item.GrossProfit2= !string.IsNullOrEmpty(item.GrossProfit2) ? item.GrossProfit2 + "%" : item.GrossProfit2;
                //if (item.OrderStatus!=null &&( item.OrderStatus == "发起人撤回" || item.OrderStatus.Contains("退回发起人")))
                //{
                //    item.OrderStatus = "处理中";
                //}
                item.SyncSAP = item.SyncSAP == "是" ? "同意" : "拒绝";
            }
            var i = 1;
            listViewDto.ForEach(x => x.SerialNumber = i++.ToString());
            return listViewDto;
        }
        #endregion
        #region KG调价报表
        /// <summary>
        /// 获取Queryable(报表展现用)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<ExportKGAdjustOutput> GetQueryableToExel(GetKGAdjustInput input)
        {
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<AdjustHeader, AdjustDetail, BusinessProcessesNew>(
                    (x, d, bu) => new JoinQueryInfos(JoinType.Inner, x.InstanceNumber == d.InstanceNumber, JoinType.Left, x.InstanceNumber == bu.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber),(x, d, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x,d, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                .WhereIF(!input.SaleRegion.IsNullOrEmpty(), (x, d, bu) => SqlFunc.ContainsArray<string>(input.SaleRegion.ToArray(), x.SaleRegion))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AutoType2), x => x.AutoType2.Equals(input.AutoType2))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                 .WhereIF(input.StartValidTime != null && input.EndValidTime != null, x => Convert.ToDateTime(x.PriceEndtime) >= input.StartValidTime && Convert.ToDateTime(x.PriceEndtime).AddDays(-1) <= input.EndValidTime)
                .Select((x, d, bu) =>
                    new ExportKGAdjustOutput()
                    {
                        InstanceNumber = x.InstanceNumber,
                        CreateTime = bu.StartTime != null ? bu.StartTime.ToString("yyyy-MM-dd HH:mm:ss") : "",
                        OrderStatus = bu.ProcessStatusName,
                        SaleRegion = x.SaleRegion,
                        AppoverType = x.AppoverType,
                        AutoType = x.AutoType,
                        AutoType2 = x.AutoType2,
                        CustomerStatus = x.CustomerStatus,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        CustomerRank = x.CustomerRank,
                        CompanyType = x.CompanyType,
                        CustomerCatalog = x.CustomerCatalog,
                        DeliverType = x.DeliverType,
                        BalanceType = x.BalanceType,
                        PriceEndtime = x.PriceEndtime,
                        ProductCode = d.ProductCode,
                        ProductName = d.ProductName,
                        SaleRegion1 = d.SaleRegion,
                        ProductType = d.ProductType,
                        SalesUnit = d.SalesUnit,
                        ConversionKG=d.ConversionKG,
                        CurrentPrice=d.CurrentPrice,
                        CurrentPriceNoTax=d.CurrentPriceNoTax.ToString(),
                        AdjustPrice=d.AdjustPrice,
                        AdjustPriceNoTax=d.AdjustPriceNoTax.ToString(),
                        AdjustPriceRange=d.AdjustPriceRange,
                        FixedMonthlyAmount = d.FixedMonthlyAmount,
                        ProcessingFee = d.ProcessingFee.ToString(),
                        ProcessingFeeRatio = d.ProcessingFeeRatio.ToString(),
                        RMC = d.RMC.ToString(),
                        RMC2 = d.RMC2.ToString(),
                        RMCRatio = d.RMCRatio.ToString(),
                        RMC2Ratio = d.RMC2Ratio.ToString(),
                        RMCRatioAfter=d.RMCRatioAfter.ToString() ,
                        RMC2RatioAfter=d.RMC2RatioAfter.ToString(),
                        RMC2ErroPrompt = d.RMC2ErroPrompt,
                        GrossProfit1 = d.GrossProfit1.ToString(),
                        GrossProfit2 = d.GrossProfit2.ToString(),
                        TheoreticalPrice = d.TheoreticalPrice.ToString(),
                        PriceEstimation1 = d.PriceEstimation1,
                        PriceEstimation2 = d.PriceEstimation2,
                        Remark = d.Remark,
                        SyncSAP = d.SyncSAP,
                        ApproveDate = bu.FinishTime != null ? bu.FinishTime.Value.ToString("yyyy-MM-dd") : "",
                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }
        /// <summary>
        /// 获取Queryable
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<KGAdjustOutput> GetQueryable(GetKGAdjustInput input)
        {
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<AdjustHeader, BusinessProcessesNew,MailSendLog>(
                    (x, bu,m) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId, JoinType.Left, x.InstanceNumber == m.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber),
                    (x, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                .WhereIF(!input.SaleRegion.IsNullOrEmpty(), (x, d, bu) => SqlFunc.ContainsArray<string>(input.SaleRegion.ToArray(), x.SaleRegion))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AutoType2), x => x.AutoType2.Equals(input.AutoType2))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                 .WhereIF(input.StartValidTime != null && input.EndValidTime != null, x => Convert.ToDateTime(x.PriceEndtime) >= input.StartValidTime && Convert.ToDateTime(x.PriceEndtime).AddDays(-1) <= input.EndValidTime)
                .Where((x, bu, m) => x.ProcessName.Equals("KG调价"))
                .Select((x, bu,m) =>
                    new KGAdjustOutput()
                    {
                        InstanceNumber = x.InstanceNumber,
                        SaleRegion = x.SaleRegion,
                        AutoType = x.AutoType,
                        CreateTime = bu.StartTime.ToString(),
                        PriceEndtime=x.PriceEndtime,
                        UserName = bu.StartUserName,
                        OrderStatus = bu.ProcessStatusName,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        SyncStatus = m.Status,
                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<KGAdjustOutput>> GetAllAsync(GetKGAdjustInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            listViewDto.ForEach(x => {
                x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
                switch (x.SyncStatus)
                {
                    case "1":
                        x.SyncStatusName = "同步失败";
                        break;
                    case "0":
                        x.SyncStatusName = "同步成功";
                        break;
                    default:
                        if (!x.OrderStatus.IsNullOrWhiteSpace() && (x.OrderStatus.Contains("终止") || x.OrderStatus.Contains("拒绝")))
                        {
                            x.SyncStatusName = "";
                        }
                        else if (!x.OrderStatus.IsNullOrWhiteSpace() && x.OrderStatus.Contains("通过"))
                        {
                            x.SyncStatusName = "暂无同步记录";
                        }
                        else
                        {
                            x.SyncStatusName = "";
                        }
                        break;
                }
            });

            //var list = listViewDto.Select(m => new KGAdjustOutput()
            //{
            //    InstanceNumber = m.InstanceNumber,
            //    CustomerName = m.CustomerName,
            //    CustomerNo = m.CustomerNo,
            //    CreateTime = m.CreateTime,
            //    UserName = m.UserName,
            //    OrderStatus = m.OrderStatus
            //}).Distinct().ToList();

            //返回结果
            return new PagedResultDto<KGAdjustOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<ExportKGAdjustOutput>> ExportAsync(GetKGAdjustInput input)
        {
            var listQueryable = GetQueryableToExel(input);
            var listViewDto = await listQueryable.ToListAsync();
            //修改导出的报表数据
            foreach (var item in listViewDto)
            {
                item.ProcessingFeeRatio = !string.IsNullOrEmpty(item.ProcessingFeeRatio) ? item.ProcessingFeeRatio + "%" : item.ProcessingFeeRatio;
                item.RMCRatio = !string.IsNullOrEmpty(item.RMCRatio) ? item.RMCRatio + "%" : item.RMCRatio;
                item.RMC2Ratio = !string.IsNullOrEmpty(item.RMC2Ratio) ? item.RMC2Ratio + "%" : item.RMC2Ratio;
                item.RMCRatioAfter = !string.IsNullOrEmpty(item.RMCRatioAfter) ? item.RMCRatioAfter + "%" : item.RMCRatioAfter;
                item.RMC2RatioAfter = !string.IsNullOrEmpty(item.RMC2RatioAfter) ? item.RMC2RatioAfter + "%" : item.RMC2RatioAfter;
                item.GrossProfit1 = !string.IsNullOrEmpty(item.GrossProfit1) ? item.GrossProfit1 + "%" : item.GrossProfit1;
                item.GrossProfit2 = !string.IsNullOrEmpty(item.GrossProfit2) ? item.GrossProfit2 + "%" : item.GrossProfit2;
                //if (item.OrderStatus != null && (item.OrderStatus == "发起人撤回" || item.OrderStatus.Contains("退回发起人")))
                //{
                //    item.OrderStatus = "处理中";
                //}
                item.SyncSAP = item.SyncSAP == "是" ? "同意" : "拒绝";
            }
            var i = 1;
            listViewDto.ForEach(x => x.SerialNumber = i++.ToString());
            return listViewDto;
        }
        #endregion
        #region CPU报价报表
        private ISugarQueryable<CPUQuotationOutput> GetQueryable(GetCPUQuotationInput input)
        {
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<QuotationHeader, BusinessProcessesNew,MailSendLog>(
                    (x, bu,m) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId, JoinType.Left, x.InstanceNumber == m.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber),
                    (x, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.SaleRegion.IsNullOrEmpty(), (x, d, bu) => SqlFunc.ContainsArray<string>(input.SaleRegion.ToArray(), x.SaleRegion))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AutoType2), x => x.AutoType2.Equals(input.AutoType2))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                 .WhereIF(!string.IsNullOrWhiteSpace(input.QuotationProductType), x => x.QuotationProductType.Equals(input.QuotationProductType))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null,
                x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .Where(x=> x.ProcessName.Equals("CPU报价"))
                .Select((x, bu, m) =>
                    new CPUQuotationOutput()
                    {
                        InstanceNumber = x.InstanceNumber,
                        SaleRegion= x.SaleRegion,
                        AutoType =x.AutoType,
                        QuotationProductType=x.QuotationProductType,
                        CreateTime = bu.StartTime.ToString(),
                        UserName = bu.StartUserName,
                        OrderStatus = bu.ProcessStatusName,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        SyncStatus=m.Status
                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<CPUQuotationOutput>> GetAllAsync(GetCPUQuotationInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            listViewDto.ForEach(x => {
                x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
                switch (x.SyncStatus)
                {
                    case "1":
                        x.SyncStatusName = "同步失败";
                        break;
                    case "0":
                        x.SyncStatusName = "同步成功";
                        break;
                    default:
                        if (!x.OrderStatus.IsNullOrWhiteSpace() && (x.OrderStatus.Contains("终止") || x.OrderStatus.Contains("拒绝")))
                        {
                            x.SyncStatusName = "";
                        }
                        else if (!x.OrderStatus.IsNullOrWhiteSpace() && x.OrderStatus.Contains("通过"))
                        {
                            x.SyncStatusName = "暂无同步记录";
                        }
                        else
                        {
                            x.SyncStatusName = "";
                        }
                        break;
                }
            });

            //返回结果
            return new PagedResultDto<CPUQuotationOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }
        #endregion

        #region CPU调价报表
        /// <summary>
        /// 获取Queryable
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<CPUAdjustOutput> GetQueryable(GetCPUAdjustInput input)
        {
            //if (!input.OrderStatus.IsNullOrEmpty() && SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), "处理中"))
            //{
            //    input.OrderStatus.Add("退回发起人（直接提交退回人）");
            //    input.OrderStatus.Add("退回发起人（逐级提交）");
            //    input.OrderStatus.Add("发起人撤回");
            //}
            var listQueryable = _db.Queryable<CPUAdjustHeader, BusinessProcessesNew, MailSendLog>(
                    (x, bu, m) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId, JoinType.Left, x.InstanceNumber == m.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber),
                    (x, bu) => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Equals(input.AddUserName))
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, bu) => SqlFunc.ContainsArray<string>(input.OrderStatus.ToArray(), bu.ProcessStatusCode))
                .WhereIF(!input.SaleRegion.IsNullOrEmpty(), (x, d, bu) => SqlFunc.ContainsArray<string>(input.SaleRegion.ToArray(), x.SaleRegion))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AppoverType), x => x.AppoverType.Equals(input.AppoverType))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.AutoType), x => x.AutoType.Equals(input.AutoType))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AutoType2), x => x.AutoType2.Equals(input.AutoType2))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName.Equals(input.CustomerName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerNo), x => x.CustomerNO.Equals(input.CustomerNo))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .Where((x, bu, m) => x.ProcessName.Equals("CPU调价及结算"))
                .Select((x, bu, m) =>
                    new CPUAdjustOutput()
                    {
                        InstanceNumber = x.InstanceNumber,
                        SaleRegion = x.SaleRegion,
                        AppoverType = x.AppoverType,
                        AutoType = x.AutoType,
                        AutoType2 = x.AutoType2,
                        BusinessOpportunities=x.BusinessOpportunities,
                        OrderStatus = bu.ProcessStatusName,
                        CustomerNo = x.CustomerNO,
                        CustomerName = x.CustomerName,
                        CustomerRank = x.CustomerRank,
                        SettlementMonth = x.SettlementMonth,
                        PriceEndtime = x.PriceEndtime.ToString().Substring(0, 10),
                        CreateTime = bu.StartTime.ToString(),
                        UserName = bu.StartUserName,
                        SyncStatus = m.Status,                      
                        ApproverName=x.ApproverName,
                        ApproveTime=x.ApproveTime,
                        XSXZZYName = x.XSXZZYName,                 
                        SettlementTotal=x.SettlementTotal,
                        SettlementAdjustTotal = x.SettlementAdjustTotal,
                        TotalRMCRatio=x.TotalRMCRatio,
                        TotalRMC2Ratio=x.TotalRMC2Ratio,
                        TotalGrossProfit1=x.TotalGrossProfit1,
                        TotalGrossProfit2=x.TotalGrossProfit2,
                    }).MergeTable().Distinct().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<CPUAdjustOutput>> GetAllAsync(GetCPUAdjustInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            listViewDto.ForEach(x => {
                x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
                switch (x.SyncStatus)
                {
                    case "1":
                        x.SyncStatusName = "同步失败";
                        break;
                    case "0":
                        x.SyncStatusName = "同步成功";
                        break;
                    default:
                        if (!x.OrderStatus.IsNullOrWhiteSpace() && (x.OrderStatus.Contains("终止") || x.OrderStatus.Contains("拒绝")))
                        {
                            x.SyncStatusName = "";
                        }
                        else if (!x.OrderStatus.IsNullOrWhiteSpace() && x.OrderStatus.Contains("通过"))
                        {
                            x.SyncStatusName = "暂无同步记录";
                        }
                        else
                        {
                            x.SyncStatusName = "";
                        }
                        break;
                }
            });

            //var list = listViewDto.Select(m => new KGAdjustOutput()
            //{
            //    InstanceNumber = m.InstanceNumber,
            //    CustomerName = m.CustomerName,
            //    CustomerNo = m.CustomerNo,
            //    CreateTime = m.CreateTime,
            //    UserName = m.UserName,
            //    OrderStatus = m.OrderStatus
            //}).Distinct().ToList();

            //返回结果
            return new PagedResultDto<CPUAdjustOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        /// <summary>
        /// 报表导出数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<CPUAdjustOutput>> ExportAsync(GetCPUAdjustInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToListAsync();

            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            listViewDto.ForEach(x => {
                x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
                switch (x.SyncStatus)
                {
                    case "1":
                        x.SyncStatusName = "同步失败";
                        break;
                    case "0":
                        x.SyncStatusName = "同步成功";
                        break;
                    default:
                        if (!x.OrderStatus.IsNullOrWhiteSpace() && (x.OrderStatus.Contains("终止") || x.OrderStatus.Contains("拒绝")))
                        {
                            x.SyncStatusName = "";
                        }
                        else if (!x.OrderStatus.IsNullOrWhiteSpace() && x.OrderStatus.Contains("通过"))
                        {
                            x.SyncStatusName = "暂无同步记录";
                        }
                        else
                        {
                            x.SyncStatusName = "";
                        }
                        break;
                }
            });

            //var list = listViewDto.Select(m => new KGAdjustOutput()
            //{
            //    InstanceNumber = m.InstanceNumber,
            //    CustomerName = m.CustomerName,
            //    CustomerNo = m.CustomerNo,
            //    CreateTime = m.CreateTime,
            //    UserName = m.UserName,
            //    OrderStatus = m.OrderStatus
            //}).Distinct().ToList();

            var i = 1;
            listViewDto.ForEach(x => x.SerialNumber = i++.ToString());
            return listViewDto;
        }

        #endregion

        private string ConvertToQueryStatus(TaskCommonStatus status, bool isInnerState = false)
        {
            string queryStatus;

            switch (status)
            {
                case TaskCommonStatus.RecallStart:
                case TaskCommonStatus.Start:
                case TaskCommonStatus.ReStart:
                case TaskCommonStatus.RejectStart:
                case TaskCommonStatus.RejectStartDirect:
                case TaskCommonStatus.RecallActivity:
                    queryStatus = "待发起";
                    break;
                case TaskCommonStatus.Processing:
                    queryStatus = "处理中";
                    break;
                case TaskCommonStatus.RejectActivity:
                case TaskCommonStatus.RejectActivityDirect:
                case TaskCommonStatus.Handover:
                case TaskCommonStatus.ExtraAppend:
                case TaskCommonStatus.ExtraInsert:
                case TaskCommonStatus.SkippedWhenEmptyResolver:
                case TaskCommonStatus.SkippedWhenSameApprover:
                    queryStatus = isInnerState ? "已完成" : "处理中";
                    break;
                case TaskCommonStatus.Done:
                    queryStatus = "通过";
                    break;
                case TaskCommonStatus.Refused:
                    queryStatus = "拒绝";
                    break;
                case TaskCommonStatus.Canceled:
                case TaskCommonStatus.InterveneAddNode:
                case TaskCommonStatus.InterveneJumpNode:
                case TaskCommonStatus.InterveneChangeUsers:
                case TaskCommonStatus.InterveneReorder:
                case TaskCommonStatus.InterveneRemoveNode:
                    queryStatus = "终止";
                    break;
                case TaskCommonStatus.Exception:
                    queryStatus = "有异常";
                    break;
                default:
                    queryStatus = "待发起";
                    break;
            }

            return queryStatus;
        }

        /// <summary>
        /// 状态名称转化成状态编码
        /// </summary>
        /// <param name="status"></param>
        /// <param name="isInnerState"></param>
        /// <returns></returns>
        private string ConvertToQueryStatusCode(string statusNme)
        {
            string queryStatusCode;

            switch (statusNme)
            {
                case "处理中":
                    queryStatusCode = "Processing";
                    break;
                case "通过":
                    queryStatusCode = "Approved";
                    break;
                case "终止":
                    queryStatusCode = "Canceled";
                    break;
                case "拒绝":
                    queryStatusCode = "Refused";
                    break;
                default:
                    queryStatusCode = "";
                    break;
            }

            return queryStatusCode;
        }
    }
}
